<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<%@ page import="Database.DBConnection" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<% int counteralt = 0;
 int counteralt1 = 0; %>
<jsp:include page="t_header.jsp" />

<script type="text/javascript">

</script>

<style type="text/css">
	div.row{
	height:50px;
	
	padding:10px 10px;
	}

	
</style>
<!--  <div class="breadcrumbs_container">
	<article class="breadcrumbs"> <a href="Proc_Process.jsp">Process</a>
	<div class="breadcrumb_divider"></div>
	<a class="current">Key Processes</a> </article>
</div>-->
<jsp:include page="header.html" />

<!-- Declare sidebar -->
<jsp:include page="t_sidebarProcess.jsp" />

<!-- Main Content -->

<section id="main" class="column"> <article
	class="module width_full" style="border: 1px solid #fafafa;">



<table class="tablesorter tableproccess" border="0px" cellspacing="0" >

<tr >
<td  class="blueprocessheader" colspan="6">
<h2 class="blueprocessh1"  >Key Processes -
	Procedures & Forms</h2>
</td>
</tr>


	<tr class="proccessth">
		<td style="width:20%">Key Process</td>
		<td style="width:13%">Code</td>
		<td style="width:20%">Procedure</td>
		<td style="width:13%">Procedure<br>Effective<br>Date
		</td>
		<td width="20%">Form</td>
		<td style="width:13%">Form<br>Effective<br>Date
		</td>

	</tr>
<% 
		String rowClass="";
		//get distinct values from processes table.
		try {
			
			 Connection connection1st = null;
			 Statement statement1st = null;
			 ResultSet rs1st = null;

			 connection1st = DBConnection.connect(true);
			 statement1st = connection1st.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			 String QueryString1st = "SELECT DISTINCT(ProcessName) from qmsdb.processes WHERE Category='KeyProcesses'";
			 rs1st = statement1st.executeQuery(QueryString1st);
			 List<String> tempProcessName=new ArrayList<String>();
			 while (rs1st.next())
			 {
				 tempProcessName.add(rs1st.getString("ProcessName"));
			 }			 
		
			// close all the connections.
			rs1st.close();
			statement1st.close();
			connection1st.close();
			 for(int t=0;t<tempProcessName.size();t++)
				{
				 counteralt1++;
				 //counteralt=0;
				//coding for retrieving database count
				 try {

				  Connection connection = null;
				  Statement statement = null;
				  ResultSet rs = null;

				  connection = DBConnection.connect(true);
				  statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				  String QueryString = "SELECT count(processName) as rowcount FROM qmsdb.processes as proccess left outer join qmsdb.forms as form on proccess.idProc = form.idProc Where processName='"+tempProcessName.get(t)+"'";
				  rs = statement.executeQuery(QueryString);
				 int count=0;
				  if (rs.next())
				  {
				  	count = rs.getInt("rowcount") ;
				  }
				  %>
				 	<tr>
				 		<td class="<% 
				 				 				 		
				 		if((counteralt1) %2 != 0)
				 			{
				 			%>oddproccessth<%
				 			}
				 		else{
				 			%>evenproccessth<%
				 		}
				 			
				 			%>" rowspan="<%=count+1 %>"><%=tempProcessName.get(t)%></td>
				 		<%
				 // close all the connections.                                                                                                              
				 rs.close();
				 statement.close();
				 connection.close();
				 }
				  catch (Exception ex) {
				 }
				 finally{
				 }
				 %>
				 		<%
				 try {
				  
				  Connection connection = null;
				  Statement statement = null;
				  ResultSet rs = null;
				  
				  connection = DBConnection.connect(true);
				  statement = connection.createStatement();
				  String QueryString = "SELECT KP.Code,KP.ProcedureName,KP.ProcedureURL,KP.ProcLastUpdate,F.FormName,F.FormURL,F.FormLastUpdate FROM qmsdb.processes AS KP LEFT OUTER JOIN qmsdb.forms AS f ON KP.idProc=F.idProc WHERE KP.ProcessName='"+tempProcessName.get(t)+"' Order by KP.Code"; 
				  rs = statement.executeQuery(QueryString);
				 String tempProcID="";
				 String tempProcURL="";
				 String tempProcLastUpdate="";
				 String tempCode="";
				 List<String> tempFormID=new ArrayList<String>();
				 List<String> tempFormURL=new ArrayList<String>();
				 List<String> tempFormLastUpdate=new ArrayList<String>();
				   while(rs.next()){  
				   	
				   	if(tempProcID.equals(rs.getString("KP.ProcedureName")))
				   	{
				   		//print add to the form
				   		tempFormID.add(rs.getString("F.FormName"));
				   		tempFormURL.add(rs.getString("F.FormURL"));
				   		tempFormLastUpdate.add(rs.getString("F.FormLastUpdate"));
				   	}
				   	else
				   	{
				   		//print out list of stored details
				   		if(tempFormID.size()>0)
				   		{
				   			counteralt++;
				   			if ((counteralt)%2 !=0)
				   				rowClass="oddproccesstr";
				   			else
				   				rowClass="evenproccesstr";
				   		%>
				   			<tr class="<%=rowClass%>">
				 			<td rowspan="<%=tempFormID.size()%>"><%=tempCode%></td>
				 			<td rowspan="<%=tempFormID.size()%>"><a href="<%=tempProcURL%>"><%=tempProcID%></a></td>
				 			<td rowspan="<%=tempFormID.size()%>"><%=tempProcLastUpdate%></td>
				 			
				 			<%
				   			for(int i=0;i<tempFormID.size();i++)
				   			{
				   				if (tempFormID.get(i)==null) {
				   				%>
				   				<td></td><td></td></tr>
				   				<%} else {
				   				if (i==0) {
				   				%>
				   					<td><a href="<%=tempFormURL.get(i)%>"><%=tempFormID.get(i)%></a></td>
				   					<td><%=tempFormLastUpdate.get(i)%></td></tr>
				   				<%} else {%>
				   					<tr class="<%=rowClass%>"><td><a href="<%=tempFormURL.get(i)%>"><%=tempFormID.get(i)%></a></td>
				   					<td><%=tempFormLastUpdate.get(i)%></td></tr>
				   				<%}

				   				}
				   			}%>
				   		<%
				   			
				   			tempFormID.clear();
				   			tempFormURL.clear();
				   			tempFormLastUpdate.clear();
				   			
				   			//Create for the current record
				   			tempProcID=rs.getString("KP.ProcedureName");
				   			tempProcURL=rs.getString("KP.ProcedureURL");
				   			tempProcLastUpdate=rs.getString("KP.ProcLastUpdate");
				   			tempCode=rs.getString("KP.Code");
				   			tempFormID.add(rs.getString("F.FormName"));
				   			tempFormURL.add(rs.getString("F.FormURL"));
				   			tempFormLastUpdate.add(rs.getString("F.FormLastUpdate"));
				   			
				   		}
				   		
				   		else 
				   		{//create new record if none of the records match.
				   			tempProcID=rs.getString("KP.ProcedureName");
				   			tempProcURL=rs.getString("KP.ProcedureURL");
				   			tempProcLastUpdate=rs.getString("KP.ProcLastUpdate");
				   			tempCode=rs.getString("KP.Code");
				   			tempFormID.add(rs.getString("F.FormName"));
				   			tempFormURL.add(rs.getString("F.FormURL"));
				   			tempFormLastUpdate.add(rs.getString("F.FormLastUpdate"));
				   		}
				   				   		
				   	}
		
				 }
				 //print the last set of records
				 %>
				 			<tr class="<% 
				 			counteralt++;
				 		if((counteralt) %2 != 0)
				 			{
				 			%>oddproccesstr<%
				 			}
				 		else{
				 			%>evenproccesstr<%
				 		}
				 			
				 			%>">
				 			<td><%=tempCode%></td>
				 			<td><a href="<%=tempProcURL%>"><%=tempProcID%></a></td>
				 			<td><%=tempProcLastUpdate%></td>
				 			<td>
				 			<%
				   			for(int i=0;i<tempFormID.size();i++)
				   			{
				   				if(tempFormID.get(i)!=null)
				   				{
				   				%><a href=<%=tempFormURL.get(i)%>><%=tempFormID.get(i)%></a><br><%
				   				}
				   				else
				   				{
				   			
				   				}
				   			}
				   			%>
				   			</td>
				   			
				   			<td>
				 			<%
				   			for(int i=0;i<tempFormLastUpdate.size();i++)
				   			{
				   				if(tempFormLastUpdate.get(i)!=null)
				   				{
				   				 %><%=tempFormLastUpdate.get(i)%><br><%
				   				}
				   				else
				   				{
				   				
				   				}
				   			}
				   			%>
				   			</td>
				   			</tr>
				   			
				 <%
				 // close all the connections.
				 rs.close();
				 statement.close();
				 connection.close();
				 }
				  catch (Exception ex) {
					  ex.printStackTrace();
				 }
				 finally{
				 }
				 %>
				 </tr>
			 <% }
			}
			 catch (Exception ex) {
			}
			finally{
			}
			
		%>
</table>

</article> 
<br /><br /></section>
<div class="spacer"></div>


<!-- Declare closing tags -->
<jsp:include page="t_footer.jsp" />